
package com.huizhan.web.dao;
 
import org.springframework.data.domain.*;
import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;
import com.huizhan.web.model.*;
import java.util.*;

@Repository 
public interface PersonalDao extends JpaRepository<Personal,String>
{
    /**
     * 根据手机号码获取用户
     * @param project_Id 项目ID
     * @param phone 手机号码
     * @return 用户信息
     */
    Personal findByProjectIdAndTypeAndPhone(String projectId,Integer type,String phone);

    /**
     * 根据手机号码获取用户
     * @param project_Id 项目ID
     * @param code 码值
     * @return 用户信息
     */
    Integer countByProjectIdAndCode(String projectId,String code);

    /**
     * 分页查询观众集合
     * @param name 姓名
     * @param phone 手机号
     * @param pageable 分页参数
     * @param project_Id 项目ID
     * @param code 码值
     * @return 观众集合
     */
    @Query(
        value = "SELECT * FROM personal"
            + " WHERE (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3  = '') "
            + " AND (code =?4 OR ?4  = '' )"
            + " AND unix_timestamp(create_date) between  ?5 and  ?6" 
            + " AND (type =?7 OR ?7 = 0) " 
            + " AND (register_Id =?8 OR ?8 = 0) " ,
        countQuery = "SELECT COUNT(1) FROM personal"
            + " WHERE (name LIKE CONCAT('%',?1,'%') OR ?1  = '' )"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3  = '') "
            + " AND (code =?4 OR ?4  = '' )"
            + " AND unix_timestamp(create_date) between  ?5 and  ?6" 
            + " AND (type =?7 OR ?7 = 0) " 
            + " AND (register_Id =?8 OR ?8 = 0) " ,
        nativeQuery = true
    )
    Page<Personal> findByWhere(String name,String phone,String projectId,String code, Long startDate,Long endDate,Long type,Long register_Id,Pageable  pageable);

}
